************************************************************************
* Build bank lender charactersitics (used to explore determinants of divestment policies)
*
***********************************************************************

* Part 1: construct a bank lending amount panel from DealScan tranches data (at parent bank level) 
use "../Raw/psedo/DealScan/tr_dealscan_newformat_tranchelevel", clear

//identify GCEL borrowers
ren Borrower_Id CompanyID_DS
joinby CompanyID_DS using ../Intermediate/dealscan_borrowerid_map, unmatched(both)
gen is_gcel_trans = (_merge == 3)
drop _merge

//get borrower industry information
rename CompanyID_DS Borrower_Id 
merge m:1 Borrower_Id using ../Raw/DealScan/tr_dealscan_newformat_borrowerdemographic, keep(1 3)
gen is_powerutil = substr(string(naics_code),1,4) == "2211"
gen is_coalmining = inlist(substr(string(naics_code),1,4),"2121","2131")
gen is_oilgas = inlist(substr(string(naics_code),1,4),"2111")
gen is_energy = is_oilgas | is_coalmining | is_powerutil 
gen is_gcel_coal = is_gcel_trans | is_coalmining
drop is_powerutil is_oilgas
keep LPC_Tranche_ID Tranche_Active_Date Tranche_Amount_Converted is_gcel_trans is_energy is_coalmining is_gcel_coal
duplicates drop 
drop if missing(LPC_Tranche_ID)

//For psedo-data only (In true data, these are already unique and the code performs nothing)
unique LPC_Tranche_ID Tranche_Active_Date
duplicates tag LPC_Tranche_ID Tranche_Active_Date, gen(tag)
drop if tag >= 1
drop tag

tempfile tranche_amt
save `tranche_amt'

use "../Raw/psedo/Dealscan/tr_dealscan_newformat_tranchelender.dta", clear
keep LPC_Tranche_ID Tranche_Active_Date Lender_Id Lender_Share 

* allocalte loan share equally among lenders in a tranche-tranche_date when lender shares are missing
egen tot_allocation_frac = sum(Lender_Share), by(LPC_Tranche_ID Tranche_Active_Date)
egen nmiss_alloc = sum(missing(Lender_Share)), by(LPC_Tranche_ID Tranche_Active_Date)
replace Lender_Share = (100-tot_allocation_frac)/nmiss_alloc if missing(Lender_Share)
replace Lender_Share = 0 if Lender_Share<0 
drop tot_allocation_frac nmiss_alloc


tempfile tranche_lendershare
save `tranche_lendershare'

use "../Raw/DealScan/tr_dealscan_newformat_lenderdemographic", clear
keep Lender_Id Lender_Operating_Country
destring Lender_Id, ignore("N/A") replace
drop if missing(Lender_Id)
duplicates drop 

tempfile lenderinfo
save `lenderinfo'


use "../Raw/Bank_Sample/Dealscan_Bank_BankSample_Link.dta",clear
ren LoanConnector_Company_ID Lender_Id

tempfile crosswalk_to_main_data
save `crosswalk_to_main_data'

* assemble datasets
use `tranche_lendershare', clear
merge m:1 Lender_Id  using `lenderinfo', nogen keep(1 3)
merge m:1 Lender_Id  using `crosswalk_to_main_data'
merge m:1 Lender_Id  using `crosswalk_to_main_data', nogen keep(3)
merge m:1 LPC_Tranche_ID Tranche_Active_Date using `tranche_amt', nogen keep(1 3)

* calculate dollar amount a bank lend in each transaction
gen lending_amt = Lender_Share/100*Tranche_Amount_Converted
gen lending_amt_gcel = lending_amt*is_gcel_trans
gen lending_amt_coal = lending_amt*is_gcel_coal
gen lending_amt_energy = lending_amt*is_energy
gen year = year(Tranche_Active_Date)

drop if missing(BankID)
* collapse to bank-year level 

//keep most common operating country by BankID 
bys BankID Lender_Operating_Country: gen nrecCountry = _N
bys BankID (nrecCountry): gen Lender_Country = Lender_Operating_Country[_N]
drop Lender_Operating_Country

collapse (sum) lending_amt lending_amt_coal lending_amt_energy lending_amt_gcel  (firstnm) Lender_Country, by(BankID year)
merge m:1 BankID using ../Raw/Bank_Sample/BankSample_List, keepusing(BankName) keep(3) nogen 
order BankName, after(BankID)

tempfile bank_lending_panel_allsample
save `bank_lending_panel_allsample'

* Part 3: Create bank characteristics variables of interest
use `bank_lending_panel_allsample', clear

// size of lending activity (in $m) for 2009-2014 period (yearly average)
gen pre_treat_period = 1 if year == 2009 | year==2010 | year==2011 | year==2012 | year==2013 | year==2014
gen post_period = year > 2014 & ~missing(year)

bys BankID: gen first = _n == 1

gen lending_amt_all = lending_amt

foreach subs in all coal energy gcel {
	di "`subs'"
	egen lending_`subs'_pre_avg = sum(lending_amt_`subs'/6) if pre_treat_period == 1, by(BankID)
	bys BankID (lending_`subs'_pre_avg): replace lending_`subs'_pre_avg = lending_`subs'_pre_avg[1]
	//post period quantities
	egen lending_`subs'_post_avg = sum(lending_amt_`subs'/7) if post_period == 1, by(BankID)
	bys BankID (lending_`subs'_post_avg): replace lending_`subs'_post_avg = lending_`subs'_post_avg[1]
	//share of pre-period and post-period lending activity in coal and broader energy
	if "`subs'" ~= "all" {
		gen share_`subs'_pre = lending_`subs'_pre_avg/lending_all_pre_avg
		gen share_`subs'_post = lending_`subs'_post_avg/lending_all_post_avg
		gen delta_share_`subs'_lvl = share_`subs'_post-share_`subs'_pre
		gen delta_share_`subs'_pct = share_`subs'_post/share_`subs'_pre-1
	}
}
gen coal_share_energy_pre = share_coal_pre/share_energy_pre
gen coal_share_energy_post = share_coal_post/share_energy_post


xtset BankID year
tssmooth ma lending_amt_coal_ma3 = lending_amt_coal, window(2 1) 

bys  BankID (year): gen CAGR_coal_lending_1014 = (lending_amt_coal/L4.lending_amt_coal)^(1/4) - 1 if year == 2014
bys  BankID (year): gen CAGR_coal_lending_1014_ma3 = (lending_amt_coal_ma3/L4.lending_amt_coal_ma3)^(1/4) - 1 if year == 2014

bys  BankID (year): gen CAGR_coal_lending_0914 = (lending_amt_coal/L5.lending_amt_coal)^(1/5) - 1 if year == 2014
bys  BankID (year): gen CAGR_coal_lending_0914_ma3 = (lending_amt_coal_ma3/L5.lending_amt_coal_ma3)^(1/5) - 1 if year == 2014

bys  BankID (year): gen CAGR_coal_lending_0915 = (lending_amt_coal/L6.lending_amt_coal)^(1/6) - 1 if year == 2015
bys  BankID (year): gen CAGR_coal_lending_0915_ma3 = (lending_amt_coal_ma3/L6.lending_amt_coal_ma3)^(1/6) - 1 if year == 2015

// convert to percentage pts and make them cross-sectional
replace CAGR_coal_lending_1014 =CAGR_coal_lending_1014*100
replace CAGR_coal_lending_1014_ma3 =CAGR_coal_lending_1014_ma3*100
replace CAGR_coal_lending_0914 = CAGR_coal_lending_0914*100
replace  CAGR_coal_lending_0914_ma3 =  CAGR_coal_lending_0914_ma3*100
replace CAGR_coal_lending_0915 = CAGR_coal_lending_0915*100
replace CAGR_coal_lending_0915_ma3 = CAGR_coal_lending_0915_ma3*100

bysort BankID (CAGR_coal_lending_1014): replace CAGR_coal_lending_1014=CAGR_coal_lending_1014[1]
bysort BankID (CAGR_coal_lending_1014_ma3): replace CAGR_coal_lending_1014_ma3=CAGR_coal_lending_1014_ma3[1]
bysort BankID (CAGR_coal_lending_0914): replace CAGR_coal_lending_0914=CAGR_coal_lending_0914[1]
bysort BankID (CAGR_coal_lending_0914_ma3): replace CAGR_coal_lending_0914_ma3=CAGR_coal_lending_0914_ma3[1]
bysort BankID (CAGR_coal_lending_0915): replace CAGR_coal_lending_0915=CAGR_coal_lending_0915[1]
bysort BankID (CAGR_coal_lending_0915_ma3): replace CAGR_coal_lending_0915_ma3=CAGR_coal_lending_0915_ma3[1]

// Delta of share of coal lending (in p.p.) for 2014-2013 v.s 2009-2010
bysort BankID (year): gen coal_lending_1314 = (lending_amt_coal+l.lending_amt_coal)/2 if year==2014
bysort BankID (year): gen coal_lending_0910 = (lending_amt_coal+l.lending_amt_coal)/2 if year==2010

bysort BankID (coal_lending_1314): replace coal_lending_1314=coal_lending_1314[1]
bysort BankID (coal_lending_0910): replace coal_lending_0910=coal_lending_0910[1]

gen delta_coal_lending_1314_0910 = [(coal_lending_1314/coal_lending_0910)-1]*100


// bank-level estimate on pre-period coal lending growth
gen log_lending = log(1+lending_amt_coal)
statsby, by(BankID) saving(../Intermediate/DS_bankcoalgrowth_estimate, replace): regress log_lending year if pre_treat_period == 1

* Part 4: collapse to cross-sectional data at bank level
drop lending_amt* pre_treat_period post_period first year log_lending
duplicates drop
duplicates report BankID 

save "../Intermediate/bank_characteristics_DS.dta", replace


************************************************************************
* Build bank lender charactersitics (used to explore determinants of divestment policies)
* with IJ Global data
***********************************************************************

* Part 1: construct a bank lending amount panel from IJ Global tranches data (at parent bank level) 

* code adopted from "build_transaction_files"

** prepare gcel borrowers
use ../Raw/IJGlobal/transaction_borrower_records, clear
recast str100 Borrower_CompanyName Borrower_ParentCompany
rename Borrower_CompanyName Borrower_CompanyName_IJG
rename Borrower_ParentCompany Borrower_ParentCompany_IJG
duplicates drop
joinby Borrower_CompanyName_IJG using ../Intermediate/ijglobal_borrowerid_map
keep TransactionId borrower_id 
duplicates drop
merge m:1 borrower_id using ../Intermediate/borrower_file, keepusing(borrower_name) keep(3) nogen
tempfile ij_borrower_map
save `ij_borrower_map'

** prepare bank link to main dataset
use  ../Raw/Bank_Sample/IJG_Bank_BankSample_Link, clear
keep IJG_BankName BankID
duplicates drop
rename IJG_BankName Lender_CompanyName
tempfile crosswalk_to_main_data 
save `crosswalk_to_main_data'

** find each lender's share in transaction
use ../Raw/psedo/IJGlobal/transaction_level_data, clear

gen is_powerutil = strpos(TransactionSector, "Power")
gen is_coalmining = strpos(TransactionSector, "Mining")
gen is_oilgas = strpos(TransactionSector, "Oil & Gas")
gen is_energy = is_oilgas | is_coalmining | is_powerutil 

keep TransactionId is_powerutil is_coalmining is_oilgas is_energy
merge 1:m TransactionId using ../Raw/psedo/IJGlobal/debt_records_tranchelevel, keepusing(TrancheId TrancheValuem TrancheMaturityStartDate) keep(3) nogen
merge 1:m TrancheId using ../Raw/psedo/IJGlobal/debt_records_tranche_lender_level, keep(3) keepusing(Lender_CompanyName Lender_CompanyName LTAccreditedValuem) nogen 

preserve
joinby TransactionId using `ij_borrower_map', unmatched(both) 
gen is_gcel_trans = (_merge == 3)
drop _merge
collapse (max) is_gcel_trans, by(TransactionId)
tempfile gceltrans 
save `gceltrans'
restore

merge m:1 TransactionId using `gceltrans'

gen is_gcel_coal = is_gcel_trans | is_coalmining
drop if missing(TrancheId)


// merge with link to main dataset
recast str Lender_CompanyName
merge m:1 Lender_CompanyName  using `crosswalk_to_main_data', nogen keep(3)

sort TransactionId TrancheId
order TransactionId TrancheId BankID Lender_CompanyName

ren LTAccreditedValuem lending_amt
gen lending_amt_gcel = lending_amt*is_gcel_trans
gen lending_amt_coal = lending_amt*is_gcel_coal
gen lending_amt_energy = lending_amt*is_energy
gen year = year(TrancheMaturityStartDate)

drop if missing(BankID)
drop if missing(year)

** collapse to a bank-year panel dataset

collapse (sum) lending_amt lending_amt_gcel lending_amt_coal lending_amt_energy, by(BankID year)
merge m:1 BankID using ../Raw/Bank_Sample/BankSample_List, keepusing(BankName) keep(3) nogen
order BankName, after(BankID)

sort BankID year

tempfile bank_lending_panel_IJsample
save `bank_lending_panel_IJsample'

* Part 2: Create bank characteristics variables of interest

use `bank_lending_panel_IJsample',clear 

gen pre_treat_period = 1 if year == 2009 | year==2010 | year==2011 | year==2012 | year==2013 | year==2014
gen post_period = year > 2014 & ~missing(year)

bys BankID: gen first = _n == 1

gen lending_amt_all = lending_amt

foreach subs in all coal energy gcel {
	di "`subs'"
	egen lending_`subs'_pre_avg = sum(lending_amt_`subs'/6) if pre_treat_period == 1, by(BankID)
	bys BankID (lending_`subs'_pre_avg): replace lending_`subs'_pre_avg = lending_`subs'_pre_avg[1]
	//post period quantities
	egen lending_`subs'_post_avg = sum(lending_amt_`subs'/7) if post_period == 1, by(BankID)
	bys BankID (lending_`subs'_post_avg): replace lending_`subs'_post_avg = lending_`subs'_post_avg[1]
	//share of pre-period and post-period lending activity in coal and broader energy
	if "`subs'" ~= "all" {
		gen share_`subs'_pre = lending_`subs'_pre_avg/lending_all_pre_avg
		gen share_`subs'_post = lending_`subs'_post_avg/lending_all_post_avg
		gen delta_share_`subs'_lvl = share_`subs'_post-share_`subs'_pre
		gen delta_share_`subs'_pct = share_`subs'_post/share_`subs'_pre-1
	}
}

gen coal_share_energy_pre = share_coal_pre/share_energy_pre
gen coal_share_energy_post = share_coal_post/share_energy_post


* Part 3: collapse to cross-sectional data at bank level and compare with bank divestment policy strength
drop lending_amt* pre_treat_period post_period first year 
duplicates drop
duplicates report BankID 

save "../Intermediate/bank_characteristics_IJ.dta", replace


************************************************************************
* AGGREGATE THESE TWO AND EXPLORE DETERMINANTS
***********************************************************************


// explore determinants of divestment policy (combine IJ Global and DS data)

use "../Intermediate/bank_characteristics_DS.dta", clear
drop CAGR* coal_lending_1314 coal_lending_0910 delta_coal_lending_1314_0910
ds BankID BankName Lender_Country, not v(20)
foreach v of var `r(varlist)' { 
	ren `v'  `v'_ds
} 

tempfile DS_var
save `DS_var'

use "../Intermediate/bank_characteristics_IJ.dta", clear
ds BankID BankName, not v(20)
foreach v of var `r(varlist)' { 
	ren `v'  `v'_ij
} 

tempfile IJ_var
save `IJ_var'

use "../Intermediate/bank_characteristics_DS.dta", clear
drop CAGR* coal_lending_1314 coal_lending_0910 delta_coal_lending_1314_0910
append using "../Intermediate/bank_characteristics_IJ.dta"
append using "../Raw/psedo/non_bank_loans_bank_ids"

collapse (firstnm) Lender_Country (sum) lending*, by(BankID)

foreach subs in coal energy gcel {
	gen share_`subs'_pre = lending_`subs'_pre_avg/lending_all_pre_avg
	gen share_`subs'_post = lending_`subs'_post_avg/lending_all_post_avg
}
gen coal_share_energy_pre = share_coal_pre/share_energy_pre
gen coal_share_energy_post = share_coal_post/share_energy_post
gen gcel_share_energy_pre =  share_gcel_pre/share_energy_pre
gen gcel_share_energy_post = share_gcel_post/share_energy_post

ds BankID Lender_Country, not v(20)
foreach v of var `r(varlist)' { 
	ren `v'  `v'_agg
} 

merge 1:1 BankID using `IJ_var', nogen keep(1 3)
merge 1:1 BankID using `DS_var', nogen keep(1 3)

//merge in dealscan data lending growth rate in pre period
merge 1:1 BankID using ../Intermediate/DS_bankcoalgrowth_estimate, keepusing(_b_year) keep(1 3)
rename _b_year pretrend_bank
drop _merge

save "../Intermediate/bank_characteristics_IJ_DS_aggregated.dta", replace


**********************************************************************************
**** NOW LOOK AT THE PORTFOLIO OF COAL BORROWERS OF EACH BANK AND GET DEMOGRAPHICS OF THE PORTFOLIO
**********************************************************************************
use ../Intermediate/bank_lending_panel, clear
egen total_coal_pre_agg = total(debt*(year<=2014)), by(BankID)
keep BankID total_coal_pre_agg 
duplicates drop
gsort -total_coal_pre_agg
tempfile total_coal_pre
save `total_coal_pre'

use ../Intermediate/bank_ban_strength_yearly, clear
gen ban_intensity_2030new1 = ban_intensity_new1 if year == 2030

//make a cross-sectional version 
ren ban_intensity_new1 ban_intensitynew1
egen complexity_score = rowtotal(is_sens*)
replace complexity_score = complexity_score/16

collapse (firstnm) ban_intensity_* (max) complexity_score projects developers relativethreshold absolutethreshold phaseout  frac_* is_* ban_intensity ban_intensitynew1 has_coded_policy has_coded_policy_new1 first_ban_year first_ban_year_new1 rf_max rf_average rf_phaseout, by(BankID)

foreach var of var ban_intensity* {
	sum `var'
	gen `var'_sd = `var'/`r(sd)'
}

//merge in bank characteristics
merge 1:1 BankID using ../Intermediate/bank_characteristics_IJ_DS_aggregated.dta, nogen
merge 1:1 BankID using ../Raw/Bank_Sample/Bank_Country.dta, nogen keep(1 3) keepusing(Region)
gen Continent = "Asia" if Region == "Asia"
replace Continent = "North America" if Region == "North America"
replace Continent = "Europe" if Region == "Europe"
replace Continent = "Others" if missing(Continent)
encode Continent, gen(nContinent)

gen loglending_pre_agg = log(lending_all_pre_avg_agg)
xtile bank_size_deciles = lending_all_pre_avg_agg, n(10)

//merge in bank coal portfolio characteristics
merge 1:1 BankID using ../Intermediate/bank_coal_pretrends, keep(1 3)
gen has_bankcoal_pretrends = _merge == 3
drop _merge

//Merge in Esg Ratings
merge 1:m BankID using "../Raw/psedo/esg_banks_with_bans", nogen keep(1 3)

//merge in coal borrowing in pre period
merge 1:1 BankID using `total_coal_pre', nogen
gen bigcoal = total_coal_pre_agg >= 1e3
gen sample_big = bigcoal == 1
gen ban_intensity_2030_comb_sd = ban_intensity_2030new1_sd
replace ban_intensity_2030_comb_sd = 0 if missing(ban_intensity_2030new1_sd)
rename ban_intensity_2030_comb_sd ban_intensity_2030
gen lending_gcel_total_avg_agg = (6/13)*lending_gcel_pre_avg_agg + (7/13)*lending_gcel_post_avg_agg

label var lending_gcel_total_avg_agg "Coal lending average volume"
label var has_coded_policy "Has Coal Policy Old"
label var has_coded_policy_new1 "Has Exit Policy"
label var share_gcel_pre_agg "Coal Share of Lending"
label var share_energy_pre_agg "Energy Share of Lending"
label var loglending_pre_agg "Bank Size"
label var bank_coal_growth_pre "Bank Coal Financing Growth"
label var _b_bank_year_0913 "Bank Coal Financing Growth"
label var _b_bank_year_0513 "Bank Coal Financing Growth"
label var coalshare "GCEL Borrowers Coal Share"
label var _b_firm_year_l1p "Coal Borrowers' Credit Growth"
label var _b_firm_year_0913 "Coal Borrowers' Credit Growth"

//save file for regressions
save "../Intermediate/policy_determinants_panel", replace
